﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;

namespace SmartSpace.Data.Util
{
    public class DbScriptUtil
    {
        public static string GetScript(EntityMetadata entity)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.AppendFormat("if exists (select 1 from  sysobjects where  id = object_id('{0}') and   type = 'U')",
                GetTableName(entity));
            sqlBuilder.AppendLine();
            sqlBuilder.AppendFormat("drop table {0};", GetTableName(entity));
            sqlBuilder.AppendLine();
            //sqlBuilder.Append("go");
            sqlBuilder.AppendLine();
            sqlBuilder.AppendFormat("CREATE TABLE {0}", GetTableName(entity));
            sqlBuilder.AppendLine();
            sqlBuilder.Append("(");
            sqlBuilder.AppendLine();

            for (int index = 0; index < entity.Properties.Count; index++)
            {
                PropertyMetadata property = entity.Properties[index];
                // name type null
                sqlBuilder.AppendFormat("{0} {1} {2},",
                    GetPropertyName(property),
                    GetPropertyType(property),
                    GetPropertyNullable(property));
                
                if (property.Type.IsComplex)
                {
                    // 复杂属性需要多一个显示文字的字段
                    sqlBuilder.AppendFormat("{0} {1} {2},",
                        GetPropertyTextName(property),
                        "nvarchar(255)",
                        GetPropertyNullable(property));
                }

                sqlBuilder.AppendLine();
            }
            sqlBuilder.AppendFormat("CONSTRAINT {0} PRIMARY KEY CLUSTERED (", GetPrimaryKeyName(entity));
            for (int index = 0; index < entity.Properties.Count; index++)
            {
                PropertyMetadata property = entity.Properties[index];
                if (property.IsIdentity)
                {
                    sqlBuilder.AppendLine();
                    sqlBuilder.AppendFormat("{0},", GetPropertyName(property));
                }
            }
            sqlBuilder = sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
            sqlBuilder.AppendLine();
            sqlBuilder.AppendLine(")) ON [PRIMARY]");

            return sqlBuilder.ToString();
        }

        public static string GetInsertScript(ViewMetadata view, DataInstance data, DbCommand command, out DbParameter[] parameters)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            List <DbParameter> parameterList = new List<DbParameter>();

            sqlBuilder.AppendFormat("INSERT INTO {0} ", GetTableName(data.Entity));
            StringBuilder pnameBuilder = new StringBuilder();
            StringBuilder pBuilder = new StringBuilder();

            for (int index = 0; index < data.Fields.Count; index++)
            {
                FieldInstance field = data.Fields[index];
                if (field.Property.IsIdentity)
                {
                    // 标识字段跳过，在最后添加
                    continue;
                }

                pnameBuilder.AppendFormat("{0},", GetPropertyName(field.Property));
                pBuilder.AppendFormat("@p{0},", index);

                DbParameter parameter = command.CreateParameter();
                parameter.ParameterName = string.Format("@p{0}", index);
                if (field.Data == null)
                {
                    parameter.Value = DBNull.Value;
                }
                else
                {
                    parameter.Value = field.Data;
                }
                parameterList.Add(parameter);

                if (field.Property.Type.IsComplex)
                {
                    pnameBuilder.AppendFormat("{0},", GetPropertyTextName(field.Property));
                    pBuilder.AppendFormat("@p{0}_text,", index);

                    DbParameter parameterText = command.CreateParameter();
                    parameterText.ParameterName = string.Format("@p{0}_text", index);
                    if (string.IsNullOrEmpty(field.Text))
                    {
                        parameterText.Value = DBNull.Value;
                    }
                    else
                    {
                        parameterText.Value = field.Text;
                    }
                    parameterList.Add(parameterText);
                }
            }


            pnameBuilder.AppendFormat("{0}", GetPropertyName(view.Entity.GetIdentity()));
            pBuilder.Append("@pid");

            DbParameter parameterId = command.CreateParameter();
            parameterId.ParameterName = "@pid";
            parameterId.Value = data.Id;
            parameterList.Add(parameterId);

            sqlBuilder.AppendFormat("({0}) VALUES ({1})", pnameBuilder, pBuilder);
            parameters = parameterList.ToArray();
            return sqlBuilder.ToString();
        }

        public static string GetUpdateScript(ViewMetadata view, DataInstance data, DbCommand command, out DbParameter[] parameters)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            List<DbParameter> parameterList = new List<DbParameter>();

            sqlBuilder.AppendFormat("UPDATE {0} SET ", GetTableName(data.Entity));
            StringBuilder pBuilder = new StringBuilder();
            int idIndex = 0;
            for (int index = 0; index < data.Fields.Count; index++)
            {
                FieldInstance field = data.Fields[index];
                if (!field.Property.IsIdentity)
                {
                    sqlBuilder.AppendFormat("{0}=", GetPropertyName(field.Property));
                    sqlBuilder.AppendFormat("@p{0},", index);

                    DbParameter parameter = command.CreateParameter();
                    parameter.ParameterName = string.Format("@p{0}", index);
                    if (field.Data == null)
                    {
                        parameter.Value = DBNull.Value;
                    }
                    else
                    {
                        parameter.Value = field.Data;
                    }
                    parameterList.Add(parameter);

                    if (field.Property.Type.IsComplex)
                    {
                        sqlBuilder.AppendFormat("{0}=", GetPropertyTextName(field.Property));
                        sqlBuilder.AppendFormat("@p{0}_text,", index);

                        DbParameter parameterText = command.CreateParameter();
                        parameterText.ParameterName = string.Format("@p{0}_text", index);
                        if (string.IsNullOrEmpty(field.Text))
                        {
                            parameterText.Value = DBNull.Value;
                        }
                        else
                        {
                            parameterText.Value = field.Text;
                        }
                        parameterList.Add(parameterText);
                    }
                }
                else
                {
                    idIndex = index;
                }
            }
            if (sqlBuilder.Length > 0)
            {
                sqlBuilder = sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
            }
            sqlBuilder.AppendFormat(" WHERE {0}=@p{1}", GetPropertyName(data.GetIdentity().Property), idIndex);
            DbParameter parameterId = command.CreateParameter();
            parameterId.ParameterName = string.Format("@p{0}", idIndex);
            parameterId.Value = data.Id;
            parameterList.Add(parameterId);

            parameters = parameterList.ToArray();

            return sqlBuilder.ToString();
        }

        public static string GetSelectScript(ViewMetadata view, Guid dataId, DbCommand command, out List<DbParameter> parameters)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            parameters = new List<DbParameter>();

            StringBuilder queryBuilder = new StringBuilder();
            StringBuilder conditionBuilder = new StringBuilder();

            for (int index = 0; index < view.Fields.Count; index++)
            {
                queryBuilder.AppendFormat("{0},", GetPropertyName(view.Fields[index].Property));
                if (view.Fields[index].Property.Type.IsComplex)
                {
                    queryBuilder.AppendFormat("{0},", GetPropertyTextName(view.Fields[index].Property));
                }
            }

            queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);
            sqlBuilder.AppendFormat(
                "select {0} from {1} where {2} =@p0",
                queryBuilder,
                GetTableName(view.Entity),
                GetPropertyName(view.Entity.GetIdentity()));

            DbParameter identityParam = command.CreateParameter();
            identityParam.ParameterName = "@p0";
            identityParam.Value = dataId;
            parameters.Add(identityParam);

            return sqlBuilder.ToString();
        }

        public static string GetPagedListScript(ViewMetadata view, int start, int limit, DbCommand command, out List<DbParameter> parameters)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            parameters = new List<DbParameter>();

            StringBuilder queryBuilder = new StringBuilder();
            StringBuilder conditionBuilder = new StringBuilder();

            for (int index = 0; index < view.Fields.Count; index++)
            {
                queryBuilder.AppendFormat("{0},", GetPropertyName(view.Fields[index].Property));
                if (view.Fields[index].Property.Type.IsComplex)
                {
                    queryBuilder.AppendFormat("{0},", GetPropertyTextName(view.Fields[index].Property));
                }
            }
            queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);
            sqlBuilder.AppendFormat(
                "select {0} from (select {0},row_number() over(order BY {1}) rowIndex from {2}) k where rowIndex between {3} and {4}",
                queryBuilder,
                GetPropertyName(view.Entity.GetIdentity()),
                GetTableName(view.Entity),
                start, // 1-25, 26-50
                start + limit - 1);

            return sqlBuilder.ToString();
        }

        public static string GetTotalScript(ViewMetadata view, DbCommand command, out List<DbParameter> parameters)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            parameters = new List<DbParameter>();

            sqlBuilder.AppendFormat(
                    "select COUNT({0}) from {1}",
                    GetPropertyName(view.Entity.GetIdentity()),
                    GetTableName(view.Entity));
            return sqlBuilder.ToString();
        }

        //public static string GetInsertScript(DetailData data, out List<object> parameters)
        //{
        //    StringBuilder sqlBuilder = new StringBuilder();
        //    parameters = new List<object>();

        //    sqlBuilder.AppendFormat("INSERT INTO {0} ", GetTableName(data.Entity));
        //    StringBuilder pnameBuilder = new StringBuilder();
        //    StringBuilder pBuilder = new StringBuilder();
        //    for (int index = 0; index < data.Fields.Count; index++)
        //    {
        //        FieldInstance field = data.Fields[index];

        //        pnameBuilder.AppendFormat("{0}", GetPropertyName(field.Property));
        //        pBuilder.Append("{" + index.ToString() + "}");
        //        parameters.Add(field.GetOriginalValue());

        //        if (index < data.Fields.Count - 1)
        //        {
        //            pnameBuilder.Append(",");
        //            pBuilder.Append(",");
        //        }
        //    }
        //    sqlBuilder.AppendFormat("({0}) VALUES ({1})", pnameBuilder, pBuilder);

        //    return sqlBuilder.ToString();
        //}

        //public static string GetUpdateScript(DetailData data, out List<object> parameters)
        //{
        //    StringBuilder sqlBuilder = new StringBuilder();
        //    parameters = new List<object>();

        //    sqlBuilder.AppendFormat("UPDATE {0} SET ", GetTableName(data.Entity));
        //    StringBuilder pnameBuilder = new StringBuilder();
        //    StringBuilder pBuilder = new StringBuilder();
        //    int idIndex = 0;
        //    for (int index = 0; index < data.Fields.Count; index++)
        //    {
        //        FieldInstance field = data.Fields[index];
        //        if (!field.Property.IsIdentity)
        //        {
        //            sqlBuilder.AppendFormat("{0}=", GetPropertyName(field.Property));
        //            sqlBuilder.Append("{" + index.ToString() + "},");
        //        }
        //        else
        //        {
        //            idIndex = index;
        //        }
        //        parameters.Add(field.GetOriginalValue());
        //    }
        //    if (parameters.Count > 0)
        //    {
        //        sqlBuilder = sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
        //    }
        //    sqlBuilder.AppendFormat(" WHERE {0}=", GetPropertyName(data.GetIdentity().Property));
        //    sqlBuilder.Append("{" + idIndex.ToString() + "}");

        //    return sqlBuilder.ToString();
        //}

        //public static string GetExistScript(DetailData data, out List<object> parameters)
        //{
        //    StringBuilder sqlBuilder = new StringBuilder();
        //    parameters = new List<object>();

        //    sqlBuilder.AppendFormat("SELECT COUNT({1}) as Count FROM {0} where {1} = ",
        //        GetTableName(data.Entity),
        //        GetPropertyName(data.GetIdentity().Property));
        //    sqlBuilder.Append("{0}");

        //    parameters.Add(data.GetIdentity().GetOriginalValue());

        //    return sqlBuilder.ToString();
        //}

        //public static string GetExistScript(DetailData data, DbCommand command, out List<DbParameter> parameters)
        //{
        //    StringBuilder sqlBuilder = new StringBuilder();
        //    parameters = new List<DbParameter>();

        //    sqlBuilder.AppendFormat("SELECT {1} FROM {0} where {1} = @p1",
        //        GetTableName(data.Entity),
        //        GetPropertyName(data.GetIdentity().Property));

        //    DbParameter parameter = command.CreateParameter();
        //    parameter.ParameterName = "@p1";
        //    parameter.Value = data.GetIdentity().GetOriginalValue();
        //    parameters.Add(parameter);

        //    return sqlBuilder.ToString();
        //}

        //public static string GetPagedListScript(ViewMetadata view, int start, int limit, out List<DbParameter> parameters)
        //{
        //    StringBuilder sqlBuilder = new StringBuilder();
        //    parameters = new List<DbParameter>();

        //    StringBuilder queryBuilder = new StringBuilder();
        //    StringBuilder conditionBuilder = new StringBuilder();

        //    for (int index = 0; index < view.Fields.Count; index++)
        //    {
        //        SmartSpace.Core.Metadata.Workflow.FieldMetadata field = view.Fields[index];
        //        if (field.IsList || field.Property.IsIdentity)
        //        {
        //            queryBuilder.AppendFormat("{0},", GetPropertyName(field.Property));
        //        }
        //    }
        //    queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);
        //    sqlBuilder.AppendFormat(
        //        "select {0} from (select {0},row_number() over(order BY {1}) rowIndex from {2}) k where rowIndex between {3} and {4}",
        //        queryBuilder,
        //        GetPropertyName(view.Entity.GetIdentity()),
        //        GetTableName(view.Entity),
        //        start, // 1-25, 26-50
        //        start + limit - 1); 

        //    return sqlBuilder.ToString();
        //}

        //public static string GetPagedListScript(ActivityMetadata activity, DbCommand command, int start, int limit, out List<DbParameter> parameters)
        //{
        //    StringBuilder sqlBuilder = new StringBuilder();
        //    parameters = new List<DbParameter>();

        //    StringBuilder queryBuilder = new StringBuilder();
        //    StringBuilder queryTempBuilder = new StringBuilder();
        //    StringBuilder conditionBuilder = new StringBuilder();

        //    for (int index = 0; index < activity.ModuleView.Fields.Count; index++)
        //    {
        //        SmartSpace.Core.Metadata.Workflow.FieldMetadata field = activity.ModuleView.Fields[index];
        //        if (field.IsList || field.Property.IsIdentity)
        //        {
        //            queryBuilder.AppendFormat("{0},", GetPropertyName(field.Property));
        //            queryTempBuilder.AppendFormat("ent.{0},", GetPropertyName(field.Property));
        //        }
        //    }
        //    queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);

        //    if (activity.IsRoot)
        //    {
        //        sqlBuilder.AppendFormat(
        //            "select {0} from (select {1} row_number() over(order BY ent.{2}) rowIndex from {3} as ent where not exists (select wi.dataid from __WorkflowInstance as wi where wi.DataId=ent.{2})) as k where rowIndex between {4} and {5}",
        //            queryBuilder,
        //            queryTempBuilder,
        //            GetPropertyName(activity.ModuleView.Entity.GetIdentity()),
        //            GetTableName(activity.ModuleView.Entity),
        //            start, // 1-25, 26-50
        //            start + limit - 1);
        //    }
        //    else
        //    {
        //        DbParameter activityIdParam = command.CreateParameter();
        //        activityIdParam.ParameterName = "@p1";
        //        activityIdParam.Value = activity.Id;
        //        parameters.Add(activityIdParam);

        //        sqlBuilder.AppendFormat(
        //            "select {0} from (select {1} row_number() over(order BY ent.{2}) rowIndex from {3} as ent where exists (select wi.dataid from __WorkflowInstance as wi where wi.DataId=ent.{2} and wi.ActivityId=@p1)) as k where rowIndex between {4} and {5}",
        //            queryBuilder,
        //            queryTempBuilder,
        //            GetPropertyName(activity.ModuleView.Entity.GetIdentity()),
        //            GetTableName(activity.ModuleView.Entity),
        //            start, // 1-25, 26-50
        //            start + limit - 1);
        //    }

        //    return sqlBuilder.ToString();
        //}

        //public static string GetSelectScript(EntityMetadata entity, Guid identity, DbCommand command, out List<DbParameter> parameters)
        //{
        //    StringBuilder sqlBuilder = new StringBuilder();
        //    parameters = new List<DbParameter>();

        //    StringBuilder queryBuilder = new StringBuilder();
        //    StringBuilder conditionBuilder = new StringBuilder();

        //    for (int index = 0; index < entity.Properties.Count; index++)
        //    {
        //        SmartSpace.Core.Metadata.PropertyMetadata property = entity.Properties[index];
        //        queryBuilder.AppendFormat("{0},", GetPropertyName(property));
        //    }
        //    queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);
        //    sqlBuilder.AppendFormat(
        //        "select {0} from {1} where {2} =@p1",
        //        queryBuilder,
        //        GetTableName(entity),
        //        GetPropertyName(entity.GetIdentity()));

        //    DbParameter identityParam = command.CreateParameter();
        //    identityParam.ParameterName = "@p1";
        //    identityParam.Value = identity;
        //    parameters.Add(identityParam);

        //    return sqlBuilder.ToString();
        //}

        ///// <summary>
        ///// 获取获得数据总数的 sql语句
        ///// </summary>
        ///// <param name="entity"></param>
        ///// <returns></returns>
        //public static string GetTotalScript(EntityMetadata entity)
        //{
        //    StringBuilder sqlBuilder = new StringBuilder();
        //    sqlBuilder.AppendFormat("SELECT COUNT({1}) as Count FROM {0}",
        //        GetTableName(entity),
        //        GetPropertyName(entity.GetIdentity()));
        //    return sqlBuilder.ToString();
        //}

        //public static string GetTotalScript(ActivityMetadata activity, DbCommand command, out List<DbParameter> parameters)
        //{
        //    StringBuilder sqlBuilder = new StringBuilder();
        //    parameters = new List<DbParameter>();

        //    if (activity.IsRoot)
        //    {
        //        sqlBuilder.AppendFormat(
        //            "select COUNT({0}) as Count from {1} as ent where not exists (select wi.dataid from __WorkflowInstance as wi where wi.DataId=ent.{0})",
        //            GetPropertyName(activity.ModuleView.Entity.GetIdentity()),
        //            GetTableName(activity.ModuleView.Entity));
        //    }
        //    else
        //    {
        //        DbParameter activityIdParam = command.CreateParameter();
        //        activityIdParam.ParameterName = "@p1";
        //        activityIdParam.Value = activity.Id;
        //        parameters.Add(activityIdParam);

        //        sqlBuilder.AppendFormat(
        //            "select COUNT({0}) as Count from {1} as ent where exists (select wi.dataid from __WorkflowInstance as wi where wi.DataId=ent.{0} and wi.ActivityId=@p1)",
        //            GetPropertyName(activity.ModuleView.Entity.GetIdentity()),
        //            GetTableName(activity.ModuleView.Entity));
        //    }

        //    return sqlBuilder.ToString();
        //}

        private static string GetTableName(EntityMetadata entity)
        {
            return string.Format("[{0}]", entity.Name);
        }

        private static string GetPrimaryKeyName(EntityMetadata entity)
        {
            return string.Format("[PK_{0}]", entity.Name);
        }

        public static string GetPropertyName(PropertyMetadata property)
        {
            return string.Format("[{0}]", property.Name);
        }

        private static string GetPropertyTextName(PropertyMetadata property)
        {
            return string.Format("[{0}_text]", property.Name);
        }

        private static string GetPropertyType(PropertyMetadata property)
        {
            TypeMetadata type = property.Type;
            if (property.MaxLength != 0)
            {
                return string.Format("[{0}]({1})", type.DbType, property.MaxLength);
            }
            else
            {
                return string.Format("[{0}]", type.DbType);
            }
        }

        private static string GetPropertyNullable(PropertyMetadata property)
        {
            if (property.IsNullable)
            {
                return "NULL";
            }
            else
            {
                return "NOT NULL";
            }
        }
    }
}
